# -*- coding: utf-8 -*-
__author__ = 'xuliliang'
# @Time    : 2018/2/7 下午2:25
# @Author  : 'xuliliang'
# @Email   : xuliliang@u51.com
# @File    : vpn.py
# @Software: PyCharm

import pymysql
from  django.conf import settings


dbhost = settings.VPN_DBHOST
dbport = settings.VPN_DBPORT
dbuser = settings.VPN_DBUSER
dbpass = settings.VPN_DBPASS
dbname = settings.VPN_DBNAME


class MysqlApi(object):
  def __init__(self):
    self.host = dbhost
    self.port = dbport
    self.user = dbuser
    self.passwd = dbpass
    self.dbname = dbname

    self.db  = pymysql.connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.dbname, charset='utf8')
    # 创建游标
    self.cursor = self.db.cursor(cursor=pymysql.cursors.DictCursor) #游标设置为字典类型
    # self.cursor = self.db.cursor() #默认是元组

  def total_count(self):
    sql = 'select * from user'
    res = self.cursor.execute(sql)
    print res
    return  res

  def get_all(self,offset,limit):
    # 这句话等同于select * from user order by create_time desc limit 0, 10;
    sql = "select * from user ORDER  BY  create_time desc limit %s,%s" % (offset,limit)
    # sql = "select * from user ORDER  BY  create_time desc "

    self.cursor.execute(sql)
    all_user = self.cursor.fetchall()
    print all_user[0]['create_time']
    return  all_user
  '''用户搜索'''
  def fetch_search(self,username,offset,limit):
##select username,email,active from user where username  LIKE '%test%' ORDER  BY  create_time desc   LIMIT 0,1
    sql = "select username,email,active from user where username LIKE '%%%s%%' ORDER  BY  create_time desc limit %s,%s" % (username,offset,limit)
    try:
      self.cursor.execute(sql)
      return  self.cursor.fetchall()
      self.cursor.close()
    except pymysql.Warning as w:
      print  w
  '''vpn用户密码修改'''
  def pass_update(self,pwd,username):
    sql = "update user set password = ENCRYPT('%s') where username = '%s' " % (pwd,username)
    try:
      return self.cursor.execute(sql)
      db.commit()
      self.cursor.close()
      self.db.close()
    except pymysql.Warning as w:

      self.db.rollback()
      return w
  '''vpn用户数据插入'''
  def user_insert(self,user_list):
    sql = "INSERT INTO user(username, password,name, expired_time) VALUES('%s', ENCRYPT('%s'),'%s', '0000-00-00 00:00:00')" % (user_list[0],user_list[1],user_list[2])
    try:
      return self.cursor.execute(sql)
      db.commit()
      self.cursor.close()
      self.db.close()
    except pymysql.Warning as w:

      self.db.rollback()
      return w
      '''vpn账号删除，传入username参数 '''
  def vpn_del(self,uid='' ):
    sql = "delete from user where username = '%s'" % (uid)
    try:
      return  self.cursor.execute(sql)
      db.commit()
      self.cursor.close()
      self.db.close()
    except pymysql.Warning as w:
      self.db.rollback()
      return w



#
# my = MysqlApi()
# print  my.get_all(10,5)
# # sql = 'select * from user'
# sql2 = "INSERT INTO user(username, password, expired_time) VALUES('test02', ENCRYPT('test02'), '0000-00-00 00:00:00')"
# print  my.sql_select(sql2)




# 执行SQL，并返回收影响行数
# effect_row = cursor.execute("select * from demo ")
# 执行SQL，并返回受影响行数
# effect_row = cursor.execute("update operation_userinfo set username = 'pengfei' where id = %s", (4))
# print  effect_row
# 执行SQL，并返回受影响行数,执行多次
# sql = "insert into hello(name) values (%s)" % (10933)
# cursor.execute(sql)
# try:
  # effect_row = cursor.executemany("insert into hello(name)values(%s)", [(93303),(01)])
  # sql = 'select * from user'

  # sql = "INSERT INTO user(username, password, expired_time) VALUES('test', ENCRYPT('test'), '0000-00-00 00:00:00')"
  # res = cursor.execute(sql)
  # print  res

  # cursor.execute(sql)
  # res = cursor.fetchall()
  # print  res['username']
  # for row in res:
    # print  row['username']
